# Subversion Info: $Id: $

"""A class that utilizes the cx_Oracle library to deliver methods that are 
required to create an interface between the application and the database."""
from PyQt4.QtCore import Qt, SIGNAL
from PyQt4.QtGui import QComboBox, QDialog, QDialogButtonBox, QGridLayout, QKeySequence, QLabel, \
                        QLineEdit, QMessageBox, QPixmap, QShortcut

import cx_Oracle
import os
import sys

OVERRIDDEN = -1

class DatabaseAuthenticator:
    @classmethod
    def getUserResponse(cls):
        dialog = DatabaseAuthenticatorDialog()
        
        if dialog.result() == QDialog.Accepted:
            return dialog.data()
        elif dialog.result() == OVERRIDDEN:
            return dialog.overridden_data()
        else:
            sys.exit()
            
class DatabaseAuthenticatorDialog(QDialog):
    def __init__(self, parent=None):
        super(DatabaseAuthenticatorDialog, self).__init__(parent)
        
        self._lm_DatabaseAuthenticator = QGridLayout()
        
        self._lblUsername = QLabel("Username:")
        self._txtUsername = QLineEdit()
        self._lblPassword = QLabel("Password:")
        self._txtPassword = QLineEdit()
        self._icoLogin = QLabel()
        self._lblDatabase = QLabel("Database:")
        self._cboAvailableDatabases = QComboBox()
        self._btnActionButtons = QDialogButtonBox(QDialogButtonBox.Ok|QDialogButtonBox.Cancel)
        
        self._initKeyboardShortcuts()
        self._initCustomStyleSheets()
        self._initGui()
        
        self.setMinimumSize(314,122)
        self.setMaximumSize(314,122)
        self.setWindowTitle("FTDB Login")
        
        self.activateWindow()
        self.show()
        self.raise_()
        
        self.exec_()
    
    def _initKeyboardShortcuts(self):
        use_local_dsn = QShortcut(QKeySequence(Qt.CTRL+Qt.SHIFT+Qt.Key_Return), self)
        
        self.connect(use_local_dsn, SIGNAL("activated()"), self._doUseLocalDsn)
    
    def _initCustomStyleSheets(self):
        self._cssActionButtons = """
            QPushButton {
                background-color: qlineargradient(x1:0, y1:0, x2:0, y2:1,
                                                  stop:0 #515151, stop: 0.5 #404040,
                                                  stop: 0.6 #333333, stop:1 #555555);
                border: 1px solid #555;
                border-radius: 1px;
                color: white;
                font-size: 12px;
                padding-left: 22px;
                padding-right: 22px;
                margin-left: 5px;
                margin-right: 5px;
                width: 55px;
                height: 20px;
            }
            
            QPushButton:pressed {
                background-color: qlineargradient(x1:0, y1:0, x2:0, y2:1,
                                                  stop:0 #818181, stop: 0.5 #707070,
                                                  stop: 0.6 #666666, stop:1 #888888);
                border: 1px solid #555;
                border-radius: 1px;
                color: white;
                font-size: 12px;
                padding-left: 22px;
                padding-right: 22px;
                margin-left: 5px;
                margin-right: 0px;
                width: 55px;
                height: 20px;
            }
            
            QPushButton:disabled {
                background-color: #555;
                border: 1px solid #555;
                border-radius: 1px;
                color: #999;
                font-size: 12px;
                padding-left: 22px;
                padding-right: 22px;
                margin-left: 5px;
                width: 55px;
                height: 20px;
            }
        """ 
        
    def _initGui(self):
        self._txtPassword.setEchoMode(QLineEdit.Password)
        self._icoLogin.setPixmap(QPixmap(os.path.join(os.getenv("FTDBRESDIR"), "img/login.png")))
        
        self._btnActionButtons.setStyleSheet(self._cssActionButtons)
        
        self._lm_DatabaseAuthenticator.addWidget(self._lblUsername, 0,0)
        self._lm_DatabaseAuthenticator.addWidget(self._txtUsername, 0,1,1,2)
        self._lm_DatabaseAuthenticator.addWidget(self._lblPassword, 1,0)
        self._lm_DatabaseAuthenticator.addWidget(self._txtPassword, 1,1,1,2)
        self._lm_DatabaseAuthenticator.addWidget(self._icoLogin, 2,0)
        self._lm_DatabaseAuthenticator.addWidget(self._btnActionButtons, 2,2)
        
        self.setLayout(self._lm_DatabaseAuthenticator)
        
        self.connect(self._btnActionButtons, SIGNAL("accepted()"), self.login)
        self.connect(self._btnActionButtons, SIGNAL("rejected()"), self.reject)
        
    def _updateAvailableActionButtons(self):
        if self._cboAvailableDatabases.currentIndex() != -1:
            self._btnActionButtons.button(QDialogButtonBox.Ok).setEnabled(True)
        else:
            self._btnActionButtons.button(QDialogButtonBox.Ok).setEnabled(False)
    
    def _updateAvailableDatabases(self):
        if self._cboAvailableDatabases.lineEdit().text() != "":
            self._cboAvailableDatabases.addItem(str(self._cboAvailableDatabases.lineEdit().text()).upper())
            self._cboAvailableDatabases.setCurrentIndex(self._cboAvailableDatabases.count()-1)
            
            self._updateAvailableActionButtons()
    
    def _doUseLocalDsn(self):
        self.accept()
        self.setResult(OVERRIDDEN)
    
    def login(self):
        self.accept()
    
    def data(self):
        return {"USERNAME": str(self._txtUsername.text()),
                "PASSWORD": str(self._txtPassword.text()),
                "DSN": "XE",
                "SCHEMA": "FTDBADM"}
    
    def overridden_data(self):
        return {"USERNAME": "ftdbuser",
                "PASSWORD": "ftdbuser!1",
                "DSN": "XE",
                "SCHEMA": "FTDBADM"}
                    
## ----------------------------------------------------------------------------

class DatabaseConnectionGateway(object):
    def __init__(self, authinfo=None, conn=None):
        """The primary database access utility class for the interface with 
        FTDB.
        
        A connection to the database is created using the login information
        provided by the C{authinfo} parameter. 
        
        I{B{Note:} The DBConnection class is thread enabled. All classes who 
        make use of this class should do all database calls in a separate 
        thread in order to keep any GUIs making database calls from freezing
        due to database calls that require extra time to complete.}
        
        @param authinfo: The data_structure holding the USERNAME, password,
            and DSN values.
        @type authinfo: <type "dict">
        
        @return: NoneType"""
        
        self._connection = None
        
        if authinfo:
            self._username = authinfo["USERNAME"]
            self._password = authinfo["PASSWORD"]
            self._dsn = authinfo["DSN"]
            self._schema = authinfo["SCHEMA"]
        
        if not conn:
            self.initConnection()
        else:
            self.setConnection(conn)
    
    def initConnection(self):
        """Creates the initial connection required to access the database.
        
        @return: NoneType"""
        
        try:
            self._connection = cx_Oracle.Connection(user=self.username(),
                                                     password=self.password(), 
                                                     dsn=self.dsn(), 
                                                     threaded=True)
        except cx_Oracle.DatabaseError:
            self._connection = cx_Oracle.Connection(user=self.username(),
                                                     password=self.password(), 
                                                     dsn=self.dsn(), 
                                                     threaded=True)
    
    
    
    def initCursor(self):
        """Creates a cursor to be used to pass calls to the database using the
        current connection object.
        
        @return: A cx_Oracle.Cursor object with the current connection
            information."""
            
        return cx_Oracle.Cursor(self._connection)
    
    def current_authinfo(self):
        """Creates a data structure storing the current login information and
        returns it to the calling process.
        
        @return: A data structure with the current USERNAME, password, and
            DSN."""
            
        return { "USERNAME":self.username(),  
                 "PASSWORD":self.password(),
                 "DSN":self.dsn(),
                 "SCHEMA": self.schema() }
    
    def setConnection(self, conn):
        self._connection = conn
    
    def connection(self):
        """Retrieve the object containing the current connection to the
        database.
        
        @return: The C{Connection} object containing the current connection to
            the database."""
            
        return self._connection
    
    def username(self):
        """The username supplied to the database during the connection process.
        
        @return: A string representation of the username."""
        
        return self._username
    
    def password(self):
        """The password supplied to the database during the connection
        process.
        
        @return: A string representation of the password."""
        
        return self._password
    
    def dsn(self):
        """The DSN supplied to the database during the connection process.
        
        @return: A string representation of the DSN."""
        
        return self._dsn
    
    def schema(self):
        """The database schema currently being accessed.
        
        @return: A string representation of the Database Schema."""
        
        return self._schema
    
    def commit(self):
        """Commit all data that has just been inserted, deleted, and/or altered
        since the last commit.
        
        @return: NoneType"""
        
        self._connection.commit()
    
    def rollback(self):
        """Revert all data back to its original form since the last commit.
        
        @return: NoneType"""
        
        self._connection.rollback()

    def close(self):
        """Close the current connection to the database.
        
        B{Note:} All calls made to the database after the connection has been
        closed will raise an C{cx_Oracle.InterfaceError: not connected}
        exception.
        
        @return: NoneType"""
         
        self._connection.close()
    
    def execute_query(self, query, data=None, cursor_input_sizes=None, doFetchOne=False, **kwargs):
        """Method used to execute query/update/delete data from the database."""     
        cursor = self.initCursor()

        if cursor_input_sizes is not None:
            eval("cursor.setinputsizes(%s)" % cursor_input_sizes)
            
        try:
            if data is not None:
                # Execute query using external data dictionary to map to bind variables
                cursor.execute(query, data)
            else:
                # Execute raw query
                cursor.execute(query)
        except cx_Oracle.InterfaceError:
            # Re-initialize the connection to the database and execute the query again            
            cursor.close()
            self.initConnection()
            
            cursor = self.initCursor()
            
            if cursor_input_sizes is not None:
                eval("cursor.setinputsizes(%s)" % cursor_input_sizes)

            if data is not None:
                cursor.execute(query, data)
            else:
                cursor.execute(query)
        except cx_Oracle.OperationalError:
            cursor.close()
            self.initConnection()
            
            cursor = self.initCursor()
            
            if cursor_input_sizes is not None:
                eval("cursor.setinputsizes(%s)" % cursor_input_sizes)
            
            if data is not None:
                cursor.execute(query, data)
            else:
                cursor.execute(query)
        except cx_Oracle.DatabaseError:
            cursor.close()
            self.initConnection()
            
            cursor = self.initCursor()
            
            if cursor_input_sizes is not None:
                eval("cursor.setinputsizes(%s)" % cursor_input_sizes)
            
            if data is not None:
                cursor.execute(query, data)
            else:
                cursor.execute(query)
        
        if not query.strip().startswith("select"):
            cursor.close()
            self.commit()
        else:
            if doFetchOne:
                results = cursor.fetchone()
            else:
                results = cursor.fetchall()
                
            cursor.close()
        
            return results

    ## -- Personnel Profile Queries -------------------------------------------
    def getPersonnelProfile(self, id):
        cursor = self.initCursor()
        
        query = """
            select id, title, first_name, last_name, org, office,
                address1, address2, city, state, zipcode, phone, email
            from %s.personnel
            where id = %d
        """ % (self.schema(), id)
        
        cursor.execute(query)
        
        results = cursor.fetchone()
        profile = {
            "id": results[0] if results[0] else "", 
            "title": results[1] if results[1] else "", 
            "first_name": results[2] if results[2] else "",
            "last_name": results[3] if results[3] else "",
            "org": results[4] if results[4] else "",
            "office": results[5] if results[5] else "",
            "address1": results[6] if results[6] else "",
            "address2": results[7] if results[7] else "",
            "city": results[8] if results[8] else "",
            "state": results[9] if results[9] else "",
            "zipcode": results[10] if results[10] else "",
            "phone": results[11] if results[11] else "",
            "email": results[12] if results[12] else ""
        }
        
        cursor.close()
        
        return profile
    
    def updatePersonnelProfile(self, profile):
        cursor = self.initCursor()
        
        query = """
            update %s.personnel
            set title = :title,
                first_name = :first_name,
                last_name = :last_name,
                org = :org,
                office = :office,
                address1 = :address1,
                address2 = :address2,
                city = :city,
                state = :state,
                zipcode = :zipcode,
                phone = :phone,
                email = :email
            where id = :id
        """ % self.schema()
        
        cursor.execute(query, profile)
        cursor.close()
        
        self.commit()
    
    def getPersonnelProfilesByGroup(self, group=None):
        cursor = self.initCursor()
        
        if group:
            query = """
                select p.id, p.title, p.first_name, p.last_name, p.org, p.office, p.address1, 
                p.address2, p.city, p.state, p.zipcode, p.phone, p.email,
                p.last_name || ', ' || p.first_name || ' ' || p.title "DISPLAY NAME"
                from %s.personnel p, %s.professional_groups pg, %s.profgrp_personnel pgp
                where p.id = pgp.personnel_id
                and pg.id = pgp.profgrp_id
                and UPPER(pg.name) = UPPER('%s')
                order by "DISPLAY NAME"
            """ % (self.schema(), self.schema(), self.schema(), group)
        else:
            query = """
                select p.id, p.title, p.first_name, p.last_name, p.org, p.office, p.address1, 
                p.address2, p.city, p.state, p.zipcode, p.phone, p.email,
                p.last_name || ', ' || p.first_name || ' ' || p.title "DISPLAY NAME"
                from %s.personnel p
                order by "DISPLAY NAME"
            """ % self.schema()
        
        cursor.execute(query)
        
        results = cursor.fetchall()
        profiles = [{
            "id": r[0] if r[0] else "", 
            "title": r[1] if r[1] else "", 
            "first_name": r[2] if r[2] else "",
            "last_name": r[3] if r[3] else "",
            "org": r[4] if r[4] else "",
            "office": r[5] if r[5] else "",
            "address1": r[6] if r[6] else "",
            "address2": r[7] if r[7] else "",
            "city": r[8] if r[8] else "",
            "state": r[9] if r[9] else "",
            "zipcode": r[10] if r[10] else "",
            "phone": r[11] if r[11] else "",
            "email": r[12] if r[12] else "",
            "display_name": r[13] if r[13] else ""
        } for r in results]
        
        cursor.close()
        
        return profiles
    
    @classmethod
    def getConnection(cls):
        dbconn = None
        attempt = 1
        
        while dbconn is None and attempt <= 3:
            try:
                dbconn = DatabaseConnectionGateway(DatabaseAuthenticator.getUserResponse())
            except cx_Oracle.DatabaseError:
                QMessageBox.critical(None, "Login Error", "%s\n\nAttempt %s" % (sys.exc_info()[1], attempt))
                
                dbconn = None
                attempt += 1
        
        if dbconn:
            return dbconn
        else:
            QMessageBox.critical(None, "Login Failed", "Too many failed login attempts")
            sys.exit(-1)
            
def main():
    from PyQt4.QtGui import QApplication
    from pprint import pprint
    from ftdb import env
    
    env.initFTDBEnvironment()
    
    app = QApplication([])
    
    dbconn = DatabaseConnectionGateway.getConnection()
    
    pprint(dbconn.getPersonnelProfilesByGroup("separation engineer"))
    
    app.exec_()
#    import random
#    
#    mid = range(1,1500)
#    random.shuffle(mid)
#    
#    for x in range(100):
#        insert_query = """
#            insert into %s.test_mission_date (aircraft_id, project_id, mission_id, mission_date)
#                values(%s, %s, %s, to_date("%d%02d%02d","YYYYMMDD"))
#        """ % (dbconn.schema(), random.randint(1,5), random.randint(1,300),
#               mid[x], random.randint(1979, 2010),
#               random.randint(1,12), random.randint(1,28))
#        
#        dbconn.execute_query(insert_query)

if __name__ == "__main__":
    main()